from matplotlib import pyplot as plt
import pandas as pd 
import statsmodels.formula.api as smf
from sqlalchemy import create_engine
import pymysql
import statsmodels.api as sm
import numpy as np
import seaborn as sns

db_config = {
    'host' : '127.0.0.1',
    'user':'root',
    'password':'root'
    'database':'tushare1','
    'port':3306,
    'charset' : 'utf8mb4'
}
engine = create_engine(
    f"mysql+pymysql://{db_config['user']}:{db_config['password']}@{db_config['host']}:{db_config['port']}/{db_config['utf8m4b']}"
)


conn = pymysql.connect(**db_config)
chunk_size = 10000
df = pd.read_sql_query("""
     SELECT d.* FROM date_1 d WHERE d.trade_date BEYWEEN '2023-01-01' and '2023-12-31' and d.ts_code = '000001.sz'"
     """,
     conn,
     chunksize=chunk_size)
df1 = pd.concat(df,ignore_index=True)

df1['zd_close'] = df1['closes'].shift(1)

df1['zs_closes'] = round((df1['closes'] - df1['closes'].shift(1)) / df1['i_closes'].shift(1),2)

df1 = df1.dropna(subset=['zd_closes','zs_closes']).reset_index(drop=True)

numeric_cols = df1.select_dtypes(include=['number']).columns.tolist()
print(df1.head)

X=df1[['buy_lg_vol','sell_lg_vol','sell_elg_vol','nut_mf_vol','zs_closes']]

eigenvalues,eigenvectors = np.linalg.eig(np.cov(X,rowvar=False))
print('累计贡献率为:',round(eigenvalues[:5].sum()/eigenvaluse.sum(),4)*100)

n_components = 5
top_eigenbectors = eigenvectors[:, :n_components]

top_eigenbectors = np.dot(X, top_eigenvectors)

principal_components = np.dot(X,top_eigenvectors)

principal_components = np.dot(X,top_eigenvectors)
data_pca = pd.concat([df1,pd.DataFrame(principal_components,
                                       columns=[f'PC{i+1}' for i in range(n_components)])],axis=1)

X_pca = data_pca[[f'PC{i+1}' for i in range(n_components)]].copy()
X_pca = sm.add_constant(X_pca)

y = df1['zd_close'].copy()

model_pca = sm.OLS(y,X_pca)

result_pca = model_pca.fit()

print("\n回归模型结果:")
print(result_pca.summary())

X_pca_selected = data_pca[['PC1','PC2','PC3']]

X_pca_selected.columns = ['PC1','PC2','PC3']

X_pca_selected = sm.add_constant(X_pca_selected)